﻿Imports Kairyu.DCEngine
Imports System.Text

Namespace SqlServer

    ''' <summary>
    ''' テーブルを生成するクラス
    ''' </summary>
    ''' <remarks></remarks>
    Public NotInheritable Class TableCreater

        Private Sub New()
        End Sub

        ''' <summary>
        ''' テーブルを生成します
        ''' </summary>
        ''' <param name="cn"></param>
        ''' <param name="tableTargetType"></param>
        ''' <remarks></remarks>
        Public Shared Sub Execute(cn As IDbConnection, tableTargetType As Type)
            TableCreater.Execute(cn, {tableTargetType})
        End Sub

        ''' <summary>
        ''' テーブルを生成します
        ''' </summary>
        ''' <param name="cn"></param>
        ''' <param name="tableTargetTypes"></param>
        ''' <remarks></remarks>
        Public Shared Sub Execute(cn As IDbConnection, tableTargetTypes As IEnumerable(Of Type))
            If cn Is Nothing Then Throw New ArgumentNullException("cn")
            If tableTargetTypes Is Nothing Then Throw New ArgumentNullException("tableTargetTypes")

            For Each t In tableTargetTypes
                Dim def = t.ToEntityDefinition
                Dim cmd = cn.CreateCommand
                cmd.CommandText = TableCreater.ToCommandText(def)
                If String.IsNullOrEmpty(cmd.CommandText) Then Continue For

                cmd.Logging.ExecuteNonQuery()
            Next
        End Sub

        ''' <summary>
        ''' コマンドテキストに変換します
        ''' </summary>
        ''' <param name="def"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Shared Function ToCommandText(def As EntityDefinition) As String
            Dim format = <s><![CDATA[
IF EXISTS (SELECT * FROM sysobjects WHERE name='{0}' AND xtype='U') DROP TABLE {0};
CREATE TABLE {0} ({1});
]]></s>.Value.Trim

            Dim tname = def.EntityName

            '列定義
            Dim ctext As New StringBuilder
            ctext.Append(TableCreater.ToColumText(def))
            If ctext.Any = False Then Return String.Empty

            '主キー定義
            Dim ktext = TableCreater.ToPKeyText(def)
            If String.IsNullOrEmpty(ktext) = False Then ctext.AppendDelimiter(",").Append(ktext)

            Dim sql = String.Format(format, tname, ctext.ToString)
            Return sql
        End Function

        ''' <summary>
        ''' 列テキストに変換します
        ''' </summary>
        ''' <param name="def"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Shared Function ToColumText(def As EntityDefinition) As String
            Dim s As New StringBuilder

            For Each item In def.LiteralProperties
                Dim ct = TableCreater.ToColumnType(item.LiteralPropertyType)
                Dim n = If(item.IsNullable, "NULL", "NOT NULL")
                s.AppendDelimiter(", ").AppendFormat("{0} {1} {2}", item.LiteralName, ct, n)

                'オートナンバー
                Dim atr = DirectCast(item.GetCustomAttributes(GetType(CommandKeyAttribute), False).FirstOrDefault, CommandKeyAttribute)
                If atr IsNot Nothing AndAlso atr.KeyType = KeyTypes.AutoNumber Then
                    s.AppendFormat(" IDENTITY(1, 1)")
                End If
            Next

            Return s.ToString
        End Function

        ''' <summary>
        ''' 主キー制約テキストに変換します
        ''' </summary>
        ''' <param name="def"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Shared Function ToPKeyText(def As EntityDefinition) As String
            Dim tname = def.EntityName
            Dim s As New StringBuilder

            For Each item In def.KeyProperties
                s.AppendDelimiter(", ").Append(item.LiteralName)
            Next

            If s.Any = False Then Return String.Empty
            Dim rtn = String.Format("CONSTRAINT PK_{0} PRIMARY KEY({1})", tname, s.ToString)
            Return rtn
        End Function

        ''' <summary>
        ''' 列タイプテキストに変換します
        ''' </summary>
        ''' <param name="t"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Shared Function ToColumnType(t As Type) As String
            If t.Equals(GetType(Boolean)) Then
                Return "bit"
            ElseIf t.Equals(GetType(Byte())) Then
                Return "image"
            ElseIf t.Equals(GetType(Char)) Then
                Return "nchar"
            ElseIf t.Equals(GetType(DateTime)) Then
                Return "datetime"
            ElseIf t.Equals(GetType(Decimal)) Then
                Return "decimal"
            ElseIf t.Equals(GetType(Double)) Then
                Return "float"
            ElseIf t.Equals(GetType(Int32)) OrElse t.IsEnum Then
                Return "int"
            ElseIf t.Equals(GetType(Int64)) Then
                Return "bigint"
            ElseIf t.Equals(GetType(Int16)) Then
                Return "smallint"
            ElseIf t.Equals(GetType(Single)) Then
                Return "real"
            ElseIf t.Equals(GetType(String)) Then
                Return "nvarchar(200)"
            Else
                Return "sql_variant"
            End If
        End Function
    End Class

End Namespace
